package ServerMain;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DataBaseConnection 
{
	private static final String DB_URL = "jdbc:mysql://localhost:3306/achievement_game";
	private static final String DB_USER = "root";
	private static final String DB_PASSWORD = "";
	
	private Properties connectionProperties;
	private Connection databaseConnection;
	
	public DataBaseConnection()
	{
		//Set database connection properties and load driver
		connectionProperties = new Properties();
		connectionProperties.put("user", DB_USER);
		connectionProperties.put("password", DB_PASSWORD);
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} 
		catch (ClassNotFoundException e) { e.printStackTrace(); } 

		//Connect to database and load data
		connectToDatabase();
	}
	
	/**
	 * Gets all achievements an user has earned
	 * @param userName - The user which will be checked.
	 * @return A list of achievements
	 */
	public String getAchievements(String userName)
	{
		String achievements = "";
		ResultSet rs = retrieveDatabaseData("SELECT * FROM user WHERE Name=\"" + userName + "\"");
		try 
		{
			if(rs.next())
			{
				//(Coulmn index starts at 1), 3 = where the achievements starts
				for(int i = 3; i < rs.getMetaData().getColumnCount() + 1; i++)
				{
					achievements += (rs.getByte(i) + "|");
				}
				return achievements;
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return "";
	}
	
	/**
	 * Checks whether an user already have a specific achievement.
	 * @param userName - The user which will be checked.
	 * @param achievementID - The achievement which will be checked.
	 * @return True if the user have the achievement.
	 */
	public boolean hasAchievement(String userName, int achievementID)
	{
		ResultSet rs = retrieveDatabaseData("SELECT * FROM user WHERE Name=\"" + userName + "\" AND TestA" + achievementID + " = true;");
		try 
		{
			return rs.next();
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Awards an user an achievement.
	 * @param userName - The user which will be awarded the achievement.
	 * @param achievementID - The achievement which will be earnt.
	 */
	public boolean awardAchievement(String userName, int achievementID)
	{
		if(containsUser(userName))
		{
			if(!hasAchievement(userName, achievementID))
			{
				manipulateDatabaseData("UPDATE user SET TestA" + achievementID + " = true WHERE Name = \"" + userName + "\"");
				return true;
			}
			System.out.println("Already has achievement");
		}
		System.out.println("Wrong user name");
		return false;
	}
	
	/**
	 * Checks if a specific user exists. Users are identified by their user name.
	 * @param userName - The user's name.
	 * @return True if the user existed in the database.
	 */
	public boolean containsUser(String userName)
	{
		ResultSet rs = retrieveDatabaseData("SELECT * FROM user WHERE Name=\"" + userName + "\";");
		try 
		{
			return rs.next();
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Creates a new user.
	 * @param userName - The new user's name.
	 * @param password - The new user's password.
	 */
	public void addUser(String userName, String password)
	{
		manipulateDatabaseData("INSERT INTO user values(\"" + userName + "\", \"" + password + "\", 0, 0, 0, 0, 0);");
	}
	
	/**
	 * Authenticates a user.
	 * @param username - The users name.
	 * @param password - The users password.
	 * @return True if the user name and password matches.
	 */
	public boolean authenticate(String userName, String password)
	{
		ResultSet rs = retrieveDatabaseData("SELECT * FROM user WHERE Name=\"" + userName + "\";");
		
		try 
		{
			//If the user exists
			if(rs.next())
			{
				//Check if password is correct
				return rs.getObject("Password").equals(password);
			}
		}
		catch (SQLException e) { e.printStackTrace(); }
		
		return false;
	}
	
	 /**
     * Connects the server to the database.
     */
	private void connectToDatabase()
	{
		try 
		{
			databaseConnection = DriverManager.getConnection(DB_URL, connectionProperties);
		}  
		catch (SQLException e) { e.printStackTrace(); }
	}
    
	/**
	 * Retrieves a ResultSet from the connected database, containing the response generated for the query parameter. This 
	 * should not be used for manipulating database data.
	 * @param query - The query used to generate the ResultSet.
	 * @return The generated ResultSet, or null if an SQLException is thrown.
	 */
	private ResultSet retrieveDatabaseData(String query)
	{
		Statement statement = null;
		try 
		{
			statement = databaseConnection.createStatement();
			return statement.executeQuery(query);
		} 
		catch (SQLException e) { e.printStackTrace(); }
		
		//Throw exception instead?
		return null;
	}
	
	/**
	 * Manipulates data in the database by executing the given query.
	 * @param query - The query used to manipulate database data.
	 */
	private void manipulateDatabaseData(String query)
	{
		Statement statement = null;
		try 
		{
			statement = databaseConnection.createStatement();
			statement.executeUpdate(query);
			
		}
		catch(SQLException e){ e.printStackTrace(); }	
	}
}